package com.dsp.week05.rest;

import com.dsp.week05.model.Demo1;
import com.dsp.week05.model.Demo2;
import com.dsp.week05.model.Demo4;
import com.dsp.week05.model.auto.Demo3;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.io.PrintWriter;
import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author dsp
 * @date 2021-04-18
 */
@RestController
public class DemoController {

    @Resource
    private Demo1 demo1;
    @Resource
    private Demo2 demo2;
    @Resource
    private Demo3 demo3;
    @Resource
    private Demo4 demo4;
    @Resource
    JdbcTemplate jdbcTemplate;

    public static final String SELECT = "select name from user";
    public static final String INSERT = "insert into user(name) values('dd')";
    public static final String INSERT_BATCH = "INSERT INTO USER VALUES(?,?,?)";
    public static final String URL = "jdbc:mysql://127.0.0.1:3306/pets?characterEncoding=utf8&useSSL=false&serverTimezone=GMT";
    public static final String USERNAME = "root";
    public static final String USERPASSWORD = "123456";
    public static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    /**
     * bean不同注入方式
     */
    @GetMapping("/demo")
    public void getDemo() {
        demo1.say();
        demo2.say();
        demo3.say();
        demo4.say();
    }

    /**
     * jdbc原生 Statement
     */
    @GetMapping("/jdbc")
    public void jdbc() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection con = DriverManager.getConnection(URL, USERNAME, USERPASSWORD);
        Statement statement = con.createStatement();
        ResultSet resultSet = statement.executeQuery(SELECT);
        while (resultSet.next()) {
            System.out.println("查询结果： " + resultSet.getString("name"));
        }
        statement.close();
        con.close();
    }

    /**
     * jdbc原生 PreparedStatement
     */
    @GetMapping("/jdbc2")
    public void jdbc2() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        Connection con = DriverManager.getConnection(URL, USERNAME, USERPASSWORD);
        PreparedStatement statement = con.prepareStatement(SELECT);
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            System.out.println("查询结果： " + resultSet.getString("name"));
        }
        statement.close();
        con.close();
    }

    /**
     * jdbc原生 事务
     */
    @GetMapping("/jdbc3")
    public void jdbc3() {
        Connection conn = null;
        Statement statement = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, USERPASSWORD);
            // 定义事务的起点
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            statement.execute(INSERT);
            // 抛出异常
            int temp = 1 / 0;
            // 未执行
            statement.execute(INSERT);
            conn.commit();
        } catch (Exception e) {
            try {
                assert conn != null;
                // 如果出现异常则回滚
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                assert statement != null;
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * jdbc原生 批量
     */
    @GetMapping("/jdbc4")
    public void jdbc4() {

        Connection conn = null;
        PreparedStatement statement = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, USERNAME, USERPASSWORD);
            // 定义事务的起点
            conn.setAutoCommit(false);
            statement = conn.prepareStatement(INSERT_BATCH);
            for (int i = 0; i < 10000; i++) {
                statement.setInt(1, i + 1);
                statement.setString(2, "name" + i);
                statement.setString(3, "password" + i);
                statement.addBatch();
            }
            statement.executeBatch();
            conn.commit();
        } catch (Exception e) {
            try {
                assert conn != null;
                // 如果出现异常则回滚
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                assert statement != null;
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @GetMapping("/hikari")
    public void hikari() {

        Properties props = new Properties();
        props.setProperty("jdbcUrl", "jdbc:mysql://127.0.0.1:3306/test?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull");
        props.setProperty("dataSource.user", "root");
        props.setProperty("dataSource.password", "123456");
        props.setProperty("dataSource.poolName", "dd-pool");
        props.setProperty("dataSource.autoCommit", "true");
        props.setProperty("dataSource.connectionTimeout", "10000");
        props.put("dataSource.logWriter", new PrintWriter(System.out));
        HikariConfig config = new HikariConfig(props);
        HikariDataSource ds = new HikariDataSource(config);
        try {
            Connection connection= ds.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select name from user");
            while (resultSet.next()) {
                System.out.println("查询结果： " + resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @GetMapping("/hikari2")
    public void hikari2() {
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select name from user");
        list.forEach(e-> System.out.println(e.get("name")));
    }



    }
